#! /bin/bash
#ods_to_dim.sh
do_date = $1
dim_user_zip_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
with new as (
    select id,
           login_name,
           nick_name,
           passwd,
           real_name,
           phone_num,
           email,
           head_img,
           user_level,
           birthday,
           gender,
           create_time,
           operate_time,
           start_date,
           end_date
    from (
             select data.id,
                    data.login_name,
                    data.nick_name,
                    data.passwd,
                    data.real_name,
                    data.phone_num,
                    data.email,
                    data.head_img,
                    data.user_level,
                    data.birthday,
                    data.gender,
                    data.create_time,
                    data.operate_time,
                    '${do_date}'     start_date,
                    '9999-12-31'     end_date,
                    row_number() over (partition by data.id order by ts desc ) rn
             from ods_user_info_inc
             where dt = '${do_date}'
         )t1 where  rn = 1
),old as (
    select id,
           login_name,
           nick_name,
           passwd,
           real_name,
           phone_num,
           email,
           head_img,
           user_level,
           birthday,
           gender,
           create_time,
           operate_time,
           status,
           dt
    from dim_user_zip where dt = '9999-12-31'
    ),full_user as (
        select id,
               login_name,
               nick_name,
               passwd,
               real_name,
               phone_num,
               email,
               head_img,
               user_level,
               birthday,
               gender,
               create_time,
               operate_time,
               start_date,
               end_date
        from new
        union
        select id,
               login_name,
               nick_name,
               passwd,
               real_name,
               phone_num,
               email,
               head_img,
               user_level,
               birthday,
               gender,
               create_time,
               operate_time,
               status,
               dt
        from old
)
insert overwrite table dim_user_zip partition (dt)
select
    id,
    login_name,
    nick_name,
    passwd,
    real_name,
    phone_num,
    email,
    head_img,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    if(rn=1,'9999-12-31', cast(date_sub('${do_date}',1) as string)),
    if(rn=1,'9999-12-31', cast(date_sub('${do_date}',1) as string))
from (
         select id,
                login_name,
                nick_name,
                passwd,
                real_name,
                phone_num,
                email,
                head_img,
                user_level,
                birthday,
                gender,
                create_time,
                operate_time,
                start_date,
                end_date,
                row_number() over (partition by id order by start_date desc ) rn
         from full_user
     )t1;
 "
/opt/module/hive/bin/hive -e "use edu;${dim_user_zip_sql};"